﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Xml;
using Microsoft.SqlServer.Management.Common;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Server;
using Nvigorate.Common;
using Nvigorate.Data;

namespace Nvigorate.Test.Data
{
    [TestClass]
    public class SMOTests
    {

        [TestMethod]
        public void TestGetTableSchemas()
        {
            var database = new SqlServerDatabase(ConfigurationManager.ConnectionStrings["simple"].ConnectionString, "nvigorate");
            var tables = database.GetTableSchemas();

            Assert.IsTrue(tables.Count() > 0);
        }

        public class SqlServerDatabase
        {
            private Server _server;
            private Database _database;

            private Dictionary<SqlDataType, Type> _typeLookup = new Dictionary<SqlDataType, Type>()
            {
                {SqlDataType.BigInt, typeof(long)},
                {SqlDataType.Bit, typeof(bool)},
                {SqlDataType.Char, typeof(string)},
                {SqlDataType.DateTime, typeof(DateTime)},
                {SqlDataType.Decimal, typeof(decimal)},
                {SqlDataType.Float, typeof(float)},
                {SqlDataType.Int, typeof(int)},
                {SqlDataType.Money, typeof(decimal)},
                {SqlDataType.NChar, typeof(string)},
                {SqlDataType.NText, typeof(string)},
                {SqlDataType.Numeric, typeof(double)},
                {SqlDataType.NVarChar, typeof(string)},
                {SqlDataType.SmallInt, typeof(short)},
                {SqlDataType.Text, typeof(string)},
                {SqlDataType.TinyInt, typeof(short)},
                {SqlDataType.UniqueIdentifier, typeof(Guid)},
                {SqlDataType.VarChar, typeof(string)},
                {SqlDataType.Xml, typeof(XmlDocument)},
            };

            public IEnumerable<Table> GetTables()
            {
                foreach (Table table in _database.Tables)
                {
                    yield return table;
                }
            }

            public IEnumerable<Tuple<string, IEnumerable<Tuple<string, Type>>>> GetTableSchemas()
            {
                return GetTables().Select(t => Tuple.Create(t.Name, GetTableSchema(t)));
            }

            private IEnumerable<Tuple<string, Type>> GetTableSchema(Table table)
            {
                foreach (Column column in table.Columns)
                {
                    yield return Tuple.Create(column.Name, _typeLookup[column.DataType.SqlDataType]);
                }
            }
            
            public SqlServerDatabase(string connectionString, string database)
            {
                var connection = new SqlConnection(connectionString);
                ServerConnection serverConnection = new ServerConnection(connection);
                _server = new Server(serverConnection);
                _database = _server.Databases[database];
            }
        }
    }
}
